<!-- build time:Tue Dec 17 2019 19:26:45 GMT+0800 (GMT+08:00) --><!doctype html><html class="theme-next mist" lang="zh-Hans"><head><meta name="generator" content="Hexo 3.8.0"><meta name="google-site-verification" content="7Tau9WyVgxnsEY9oYedu9g0U6_8akOX3wiKbaYcrg9A"><meta name="baidu-site-verification" content="EVwLiaxdxX"><link href="/css/xps13.css" rel="stylesheet" type="text/css"><link href="/css/message.css" rel="stylesheet" type="text/css"><script type="text/javascript" src="/js/jquery-1.11.3.min.js"></script><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css"><link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css"><meta name="keywords" content="DataBase,Oracle,Oracle 11g,"><link rel="alternate" href="/atom.xml" title="MrBird" type="application/atom+xml"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1"><meta name="description" content="Oracle 中的分析函数基于对数据行的分组来计算相关值，类似于聚合函数。其和聚合函数主要的区别在于：分析函数对于每个分组返回多行数据，而聚合函数每个分组只能对应一行数据。分析函数由三个部分组成：分区子句、排序子句和开窗子句，基本语法如下：12function(arg1,arg2,...argN)over([partition-by-clause] [order-by-clause] [windo"><meta name="keywords" content="DataBase,Oracle,Oracle 11g"><meta property="og:type" content="article"><meta property="og:title" content="Oracle分析函数（窗口函数）"><meta property="og:url" content="http://mrbird.cc/oracle Window function.html"><meta property="og:site_name" content="MrBird"><meta property="og:description" content="Oracle 中的分析函数基于对数据行的分组来计算相关值，类似于聚合函数。其和聚合函数主要的区别在于：分析函数对于每个分组返回多行数据，而聚合函数每个分组只能对应一行数据。分析函数由三个部分组成：分区子句、排序子句和开窗子句，基本语法如下：12function(arg1,arg2,...argN)over([partition-by-clause] [order-by-clause] [windo"><meta property="og:locale" content="zh-Hans"><meta property="og:updated_time" content="2019-10-28T12:14:46.305Z"><meta name="twitter:card" content="summary"><meta name="twitter:title" content="Oracle分析函数（窗口函数）"><meta name="twitter:description" content="Oracle 中的分析函数基于对数据行的分组来计算相关值，类似于聚合函数。其和聚合函数主要的区别在于：分析函数对于每个分组返回多行数据，而聚合函数每个分组只能对应一行数据。分析函数由三个部分组成：分区子句、排序子句和开窗子句，基本语法如下：12function(arg1,arg2,...argN)over([partition-by-clause] [order-by-clause] [windo"><script type="text/javascript" id="hexo.configurations">var NexT=window.NexT||{},CONFIG={root:"/",scheme:"Mist",sidebar:{position:"left",display:"always",offset:12,offset_float:0,b2t:!1,scrollpercent:!1},fancybox:!1,motion:!1}</script><title>Oracle分析函数（窗口函数） | MrBird</title></head><body ondragstart="return!1" class="animsition" lang="zh-Hans" style="overflow-x:hidden;padding-right:280px"><script type="text/javascript" src="/js/mo.min.js"></script><style>@media (min-width:768px) and (max-width:991px){body .header-innerr{width:700px!important}}.header-innerr{margin:0 auto;padding:100px 0 70px;width:880px}@media (min-width:1600px){.container .header-innerr{width:1200px}}.header-innerr{position:relative}.header-innerr{padding:20px 0 0}.header-innerr:after,.header-innerr:before{content:" ";display:table}.header-innerr:after{clear:both}@media (max-width:767px){.header-innerr{width:auto;padding:10px;margin-bottom:-20px}}</style><div class="container sidebar-position-left page-post-detail"><div class="headband"></div><header id="header" class="header"><div class="header-inner"><div class="site-brand-wrapper"><div class="site-meta"><link href="https://fonts.font.im/css?family=Merienda" rel="stylesheet"><div class="custom-logo-site-title"><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title" style="font-family:Merienda;font-size:1.3rem">MrBird</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle"></p></div><div class="site-nav-toggle"><button><span class="btn-bar"></span> <span class="btn-bar"></span> <span class="btn-bar"></span></button></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section">HOME</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section">ARCHIVES</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section">TAGS</a></li><li class="menu-item menu-item-friends"><a href="/friends/" rel="section">FRIENDS</a></li><div class="sidebar-toggle" style="display:none"><div class="sidebar-toggle-line-wrap"><span class="sidebar-toggle-line sidebar-toggle-line-first"></span> <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span> <span class="sidebar-toggle-line sidebar-toggle-line-last"></span></div></div></ul><div class="site-search"><div class="popup search-popup local-search-popup"><div class="local-search-header clearfix"><span class="search-icon"><i class="fa fa-search"></i> </span><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span><div class="local-search-input-wrapper"><input autocomplete="off" placeholder="Search" spellcheck="false" type="text" id="local-search-input"></div></div><div id="local-search-result"></div></div></div></nav></div><div class="header-innerr"><div class="note info" style="margin:0;letter-spacing:.15px">🐤手把手教你搭建<strong>Spring Cloud微服务权限系统</strong>（从零到部署）：<a style="color:#40dab2;font-weight:600" href="https://www.kancloud.cn/mrbird/spring-cloud" target="_blank">https://www.kancloud.cn/mrbird/spring-cloud</a></div></div></header><main id="main" class="main"><div class="main-inner"><div class="content-wrap"><div id="content" class="content"><div id="posts" class="posts-expand"><article class="post post-type-normal" itemscope itemtype="http://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://mrbird.cc/oracle Window function.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="name" content="MrBird"><meta itemprop="description" content=""><meta itemprop="image" content="/images/blogImage.jpg"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="MrBird"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">Oracle分析函数（窗口函数）</h1><div class="post-meta"><span class="post-time"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">Posted on</span> <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-11-10T14:40:47+08:00">2017-11-10 </time></span><span></span> <span class="post-meta-divider">|</span> <span class="page-pv"><i class="fa fa-laptop"></i>&nbsp;&nbsp;Visit count <span class="busuanzi-value" id="busuanzi_value_page_pv"></span></span></div></header><div class="post-body" itemprop="articleBody"><p>Oracle 中的分析函数基于对数据行的分组来计算相关值，类似于聚合函数。其和聚合函数主要的区别在于：分析函数对于每个分组返回多行数据，而聚合函数每个分组只能对应一行数据。</p><p>分析函数由三个部分组成：分区子句、排序子句和开窗子句，基本语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">function(arg1,arg2,...argN)</span><br><span class="line">over([partition-by-clause] [order-by-clause] [windowing-clause])</span><br></pre></td></tr></table></figure><p></p><a id="more"></a><p>开窗子句windowing-clause 指定了分析函数进行运算的数据子集。开窗子句的具体语法如下：</p><figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">[rows | range] between &lt;start expr&gt; and &lt;end expr&gt;</span><br><span class="line"></span><br><span class="line">&lt;start expr&gt; is [unbounded preceding | current row | n preceding | n following]</span><br><span class="line">&lt;end expr&gt; is [unbounded following | current row | n preceding | n following]</span><br></pre></td></tr></table></figure><p></p><ul><li><p><code>unbounded preceding</code>表示以分组数据的第一行最为上边界；</p></li><li><p><code>unbounded following</code>表示以分组数据的最后一行最为下边界；</p></li><li><p><code>current row</code>表示当前数据行；</p></li><li><p><code>n preceding</code>表示当前数据行的前n 行；</p></li><li><p><code>n following</code>表示当前数据行的后n 行。</p></li></ul><p>为了演示分析函数，首先在SH用户下创建一张sales_fact表：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table sales_fact as</span><br><span class="line">  <span class="keyword">select</span> country_name country,country_subRegion region,prod_name product,</span><br><span class="line">  calendar_year <span class="keyword">year</span>,calendar_week_number <span class="keyword">week</span>,</span><br><span class="line">  <span class="keyword">sum</span>(amount_sold) sale,</span><br><span class="line">  <span class="keyword">sum</span>(amount_sold*( <span class="keyword">case</span></span><br><span class="line">   <span class="keyword">when</span> <span class="keyword">mod</span>(<span class="keyword">rownum</span>,<span class="number">10</span>)=<span class="number">0</span> <span class="keyword">then</span> <span class="number">1.4</span></span><br><span class="line">   <span class="keyword">when</span> <span class="keyword">mod</span>(<span class="keyword">rownum</span>,<span class="number">5</span>)=<span class="number">0</span> <span class="keyword">then</span> <span class="number">0.6</span></span><br><span class="line">   <span class="keyword">when</span> <span class="keyword">mod</span>(<span class="keyword">rownum</span>,<span class="number">2</span>)=<span class="number">0</span> <span class="keyword">then</span> <span class="number">0.9</span></span><br><span class="line">   <span class="keyword">when</span> <span class="keyword">mod</span>(<span class="keyword">rownum</span>,<span class="number">2</span>)=<span class="number">1</span> <span class="keyword">then</span> <span class="number">1.2</span></span><br><span class="line">   <span class="keyword">else</span> <span class="number">1</span> <span class="keyword">end</span>)) receipts</span><br><span class="line">  <span class="keyword">from</span> sales,times,customers,countries,products</span><br><span class="line">  <span class="keyword">where</span> sales.time_id=times.time_id <span class="keyword">and</span></span><br><span class="line">  sales.prod_id = products.prod_id <span class="keyword">and</span></span><br><span class="line">  sales.cust_id = customers.cust_id <span class="keyword">and</span></span><br><span class="line">  customers.country_id = countries.country_id</span><br><span class="line">  <span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">  country_name,country_subRegion,prod_name,calendar_year,calendar_week_number;</span><br><span class="line"></span><br><span class="line">表已创建。</span><br></pre></td></tr></table></figure><p></p><h2 id="分析模式下的聚合函数"><a href="#分析模式下的聚合函数" class="headerlink" title="分析模式下的聚合函数"></a>分析模式下的聚合函数</h2><p>前面所说聚合函数每个分组只能对应一行数据是在传统的非分析模式下的结果。分析模式下的聚合函数则没有此限制。</p><p>比如下面的SQL语句计算了sale列按照产品，国家，地区和年份为一组，从每年年初开始到该年份每一周的动态求和值。（为了节约篇幅，下面结果只取前10周结果集）。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">  sum(sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by week</span><br><span class="line">   rows between unbounded preceding and current row</span><br><span class="line">  ) running_sum_sale</span><br><span class="line">  from sales_fact</span><br><span class="line">  where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">  order by year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE RUNNING_SUM_SALE</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------------</span></span><br><span class="line">      1998          1      58.15            58.15</span><br><span class="line">      1998          2      29.39            87.54</span><br><span class="line">      1998          3      29.49           117.03</span><br><span class="line">      1998          4      29.49           146.52</span><br><span class="line">      1998          5       29.8           176.32</span><br><span class="line">      1998          6      58.78            235.1</span><br><span class="line">      1998          9      58.78           293.88</span><br><span class="line">      1998         10     117.76           411.64</span><br><span class="line">      1999          1      53.52            53.52</span><br><span class="line">      1999          3       94.6           148.12</span><br><span class="line">      1999          4       40.5           188.62</span><br><span class="line">      1999          5      80.01           268.63</span><br><span class="line">      1999          6       40.5           309.13</span><br><span class="line">      1999          8     103.11           412.24</span><br><span class="line">      1999          9      53.34           465.58</span><br><span class="line">      1999         10         72           537.58</span><br><span class="line">      2000          1       46.7             46.7</span><br><span class="line">      2000          3      93.41           140.11</span><br><span class="line">      2000          4      46.54           186.65</span><br><span class="line">      2000          5       46.7           233.35</span><br><span class="line">      2000          7       70.8           304.15</span><br><span class="line">      2000          8      46.54           350.69</span><br><span class="line">      2001          1      92.26            92.26</span><br><span class="line">      2001          2     118.38           210.64</span><br><span class="line">      2001          3      47.24           257.88</span><br><span class="line">      2001          4      256.7           514.58</span><br><span class="line">      2001          5      93.44           608.02</span><br><span class="line">      2001          6      22.44           630.46</span><br><span class="line">      2001          7      69.96           700.42</span><br><span class="line">      2001          8      46.06           746.48</span><br><span class="line">      2001          9      92.67           839.15</span><br><span class="line">      2001         10      69.05            908.2</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><p>聚合函数<code>sum(sale)</code>声明要求和的列，<code>partition by product,country,region,year</code>分区子句声明了分组的列，<code>order by week</code>排序子句声明了分组的数据行按照week列来进行排序，<code>rows between unbounded preceding and current row</code>开窗子句声明了计算的窗口范围为分组的第一行到当前行。</p><p>下面例子的窗口范围为整个分组：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select year,week,sale,</span><br><span class="line">  max(sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by week</span><br><span class="line">   rows between unbounded preceding and unbounded following</span><br><span class="line">  ) max_sal_per_year</span><br><span class="line">  from sales_fact</span><br><span class="line">  where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">  order by year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE MAX_SAL_PER_YEAR</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------------</span></span><br><span class="line">      1998          1      58.15           117.76</span><br><span class="line">      1998          2      29.39           117.76</span><br><span class="line">      1998          3      29.49           117.76</span><br><span class="line">      1998          4      29.49           117.76</span><br><span class="line">      1998          5       29.8           117.76</span><br><span class="line">      1998          6      58.78           117.76</span><br><span class="line">      1998          9      58.78           117.76</span><br><span class="line">      1998         10     117.76           117.76</span><br><span class="line">      1999          1      53.52           103.11</span><br><span class="line">      1999          3       94.6           103.11</span><br><span class="line">      1999          4       40.5           103.11</span><br><span class="line">      1999          5      80.01           103.11</span><br><span class="line">      1999          6       40.5           103.11</span><br><span class="line">      1999          8     103.11           103.11</span><br><span class="line">      1999          9      53.34           103.11</span><br><span class="line">      1999         10         72           103.11</span><br><span class="line">      2000          1       46.7            93.41</span><br><span class="line">      2000          3      93.41            93.41</span><br><span class="line">      2000          4      46.54            93.41</span><br><span class="line">      2000          5       46.7            93.41</span><br><span class="line">      2000          7       70.8            93.41</span><br><span class="line">      2000          8      46.54            93.41</span><br><span class="line">      2001          1      92.26            256.7</span><br><span class="line">      2001          2     118.38            256.7</span><br><span class="line">      2001          3      47.24            256.7</span><br><span class="line">      2001          4      256.7            256.7</span><br><span class="line">      2001          5      93.44            256.7</span><br><span class="line">      2001          6      22.44            256.7</span><br><span class="line">      2001          7      69.96            256.7</span><br><span class="line">      2001          8      46.06            256.7</span><br><span class="line">      2001          9      92.67            256.7</span><br><span class="line">      2001         10      69.05            256.7</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><p>下面例子的窗口范围为当前周的前两周和后两周，也就是五周。在分组的边界处，窗口会自动缩小：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select year,week,sale,</span><br><span class="line">  max(sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by week</span><br><span class="line">   rows between 2 preceding and 2 following</span><br><span class="line">  ) max_sal_per_year</span><br><span class="line">  from sales_fact</span><br><span class="line">  where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">  order by year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE MAX_SAL_PER_YEAR</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------------</span></span><br><span class="line">      1998          1      58.15            58.15</span><br><span class="line">      1998          2      29.39            58.15</span><br><span class="line">      1998          3      29.49            58.15</span><br><span class="line">      1998          4      29.49            58.78</span><br><span class="line">      1998          5       29.8            58.78</span><br><span class="line">      1998          6      58.78           117.76</span><br><span class="line">      1998          9      58.78           117.76</span><br><span class="line">      1998         10     117.76           117.76</span><br><span class="line">      1999          1      53.52             94.6</span><br><span class="line">      1999          3       94.6             94.6</span><br><span class="line">      1999          4       40.5             94.6</span><br><span class="line">      1999          5      80.01           103.11</span><br><span class="line">      1999          6       40.5           103.11</span><br><span class="line">      1999          8     103.11           103.11</span><br><span class="line">      1999          9      53.34           103.11</span><br><span class="line">      1999         10         72           103.11</span><br><span class="line">      2000          1       46.7            93.41</span><br><span class="line">      2000          3      93.41            93.41</span><br><span class="line">      2000          4      46.54            93.41</span><br><span class="line">      2000          5       46.7            93.41</span><br><span class="line">      2000          7       70.8             70.8</span><br><span class="line">      2000          8      46.54             70.8</span><br><span class="line">      2001          1      92.26           118.38</span><br><span class="line">      2001          2     118.38            256.7</span><br><span class="line">      2001          3      47.24            256.7</span><br><span class="line">      2001          4      256.7            256.7</span><br><span class="line">      2001          5      93.44            256.7</span><br><span class="line">      2001          6      22.44            256.7</span><br><span class="line">      2001          7      69.96            93.44</span><br><span class="line">      2001          8      46.06            92.67</span><br><span class="line">      2001          9      92.67            92.67</span><br><span class="line">      2001         10      69.05            92.67</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><div class="note danger"><p>默认的窗口子句是<code>rows between unbounded preceding and current row</code>。</p></div><h2 id="lag和lead"><a href="#lag和lead" class="headerlink" title="lag和lead"></a>lag和lead</h2><p>lag和lead函数能够实现跨行引用。lag能够访问结果集中前面行内容，lead能够访问结果集中后面行内容。</p><div class="note danger"><p>lag和lead函数不支持开窗子句，仅支持<code>partition by</code>和<code>order by</code>子句。</p></div><h3 id="lag"><a href="#lag" class="headerlink" title="lag"></a>lag</h3><p>lag函数的语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">lag(expression, offset, default) over(partition-clause order-by-clause)</span><br></pre></td></tr></table></figure><p></p><p><code>expression</code>表示返回的列，<code>offset</code>表示相隔的行数（不能为负数），default表示默认值。</p><p>比如，从前一行中返回一个值：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   lag(sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by week</span><br><span class="line">   ) prior_wk_sales</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE PRIOR_WK_SALES</span><br><span class="line"><span class="comment">---------- ---------- ---------- --------------</span></span><br><span class="line">      1998          1      58.15</span><br><span class="line">      1998          2      29.39          58.15</span><br><span class="line">      1998          3      29.49          29.39</span><br><span class="line">      1998          4      29.49          29.49</span><br><span class="line">      1998          5       29.8          29.49</span><br><span class="line">      1998          6      58.78           29.8</span><br><span class="line">      1998          9      58.78          58.78</span><br><span class="line">      1998         10     117.76          58.78</span><br><span class="line">      1999          1      53.52</span><br><span class="line">      1999          3       94.6          53.52</span><br><span class="line">      1999          4       40.5           94.6</span><br><span class="line">      1999          5      80.01           40.5</span><br><span class="line">      1999          6       40.5          80.01</span><br><span class="line">      1999          8     103.11           40.5</span><br><span class="line">      1999          9      53.34         103.11</span><br><span class="line">      1999         10         72          53.34</span><br><span class="line">      2000          1       46.7</span><br><span class="line">      2000          3      93.41           46.7</span><br><span class="line">      2000          4      46.54          93.41</span><br><span class="line">      2000          5       46.7          46.54</span><br><span class="line">      2000          7       70.8           46.7</span><br><span class="line">      2000          8      46.54           70.8</span><br><span class="line">      2001          1      92.26</span><br><span class="line">      2001          2     118.38          92.26</span><br><span class="line">      2001          3      47.24         118.38</span><br><span class="line">      2001          4      256.7          47.24</span><br><span class="line">      2001          5      93.44          256.7</span><br><span class="line">      2001          6      22.44          93.44</span><br><span class="line">      2001          7      69.96          22.44</span><br><span class="line">      2001          8      46.06          69.96</span><br><span class="line">      2001          9      92.67          46.06</span><br><span class="line">      2001         10      69.05          92.67</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><p>可见lag函数在分区的上边界返回空值，默认行数为1。</p><p>指定lag函数的默认值，并指定行数为3：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   lag(sale,3,sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by week</span><br><span class="line">   ) prior_wk_sales</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE PRIOR_WK_SALES</span><br><span class="line"><span class="comment">---------- ---------- ---------- --------------</span></span><br><span class="line">      1998          1      58.15          58.15</span><br><span class="line">      1998          2      29.39          29.39</span><br><span class="line">      1998          3      29.49          29.49</span><br><span class="line">      1998          4      29.49          58.15</span><br><span class="line">      1998          5       29.8          29.39</span><br><span class="line">      1998          6      58.78          29.49</span><br><span class="line">      1998          9      58.78          29.49</span><br><span class="line">      1998         10     117.76           29.8</span><br><span class="line">      1999          1      53.52          53.52</span><br><span class="line">      1999          3       94.6           94.6</span><br><span class="line">      1999          4       40.5           40.5</span><br><span class="line">      1999          5      80.01          53.52</span><br><span class="line">      1999          6       40.5           94.6</span><br><span class="line">      1999          8     103.11           40.5</span><br><span class="line">      1999          9      53.34          80.01</span><br><span class="line">      1999         10         72           40.5</span><br><span class="line">      2000          1       46.7           46.7</span><br><span class="line">      2000          3      93.41          93.41</span><br><span class="line">      2000          4      46.54          46.54</span><br><span class="line">      2000          5       46.7           46.7</span><br><span class="line">      2000          7       70.8          93.41</span><br><span class="line">      2000          8      46.54          46.54</span><br><span class="line">      2001          1      92.26          92.26</span><br><span class="line">      2001          2     118.38         118.38</span><br><span class="line">      2001          3      47.24          47.24</span><br><span class="line">      2001          4      256.7          92.26</span><br><span class="line">      2001          5      93.44         118.38</span><br><span class="line">      2001          6      22.44          47.24</span><br><span class="line">      2001          7      69.96          256.7</span><br><span class="line">      2001          8      46.06          93.44</span><br><span class="line">      2001          9      92.67          22.44</span><br><span class="line">      2001         10      69.05          69.96</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h3 id="lead"><a href="#lead" class="headerlink" title="lead"></a>lead</h3><p>lead和lag类似。不再赘述。</p><h2 id="first-value和last-value"><a href="#first-value和last-value" class="headerlink" title="first_value和last_value"></a>first_value和last_value</h2><p>first_value和last_value函数通常与order by语句配合来筛选出分区中的最大值和最小值。它们都支持开窗子句。</p><h3 id="first-value"><a href="#first-value" class="headerlink" title="first_value"></a>first_value</h3><p>first_value返回窗口中的第一个值。ignore nulls表示忽略空值，如果第一个是空值返回第二个：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   first_value(sale ignore nulls) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale desc</span><br><span class="line">   rows between unbounded preceding and unbounded following</span><br><span class="line">   ) max_sale</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE   MAX_SALE</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      1998          1      58.15     117.76</span><br><span class="line">      1998          2      29.39     117.76</span><br><span class="line">      1998          3      29.49     117.76</span><br><span class="line">      1998          4      29.49     117.76</span><br><span class="line">      1998          5       29.8     117.76</span><br><span class="line">      1998          6      58.78     117.76</span><br><span class="line">      1998          9      58.78     117.76</span><br><span class="line">      1998         10     117.76     117.76</span><br><span class="line">      1999          1      53.52     103.11</span><br><span class="line">      1999          3       94.6     103.11</span><br><span class="line">      1999          4       40.5     103.11</span><br><span class="line">      1999          5      80.01     103.11</span><br><span class="line">      1999          6       40.5     103.11</span><br><span class="line">      1999          8     103.11     103.11</span><br><span class="line">      1999          9      53.34     103.11</span><br><span class="line">      1999         10         72     103.11</span><br><span class="line">      2000          1       46.7      93.41</span><br><span class="line">      2000          3      93.41      93.41</span><br><span class="line">      2000          4      46.54      93.41</span><br><span class="line">      2000          5       46.7      93.41</span><br><span class="line">      2000          7       70.8      93.41</span><br><span class="line">      2000          8      46.54      93.41</span><br><span class="line">      2001          1      92.26      256.7</span><br><span class="line">      2001          2     118.38      256.7</span><br><span class="line">      2001          3      47.24      256.7</span><br><span class="line">      2001          4      256.7      256.7</span><br><span class="line">      2001          5      93.44      256.7</span><br><span class="line">      2001          6      22.44      256.7</span><br><span class="line">      2001          7      69.96      256.7</span><br><span class="line">      2001          8      46.06      256.7</span><br><span class="line">      2001          9      92.67      256.7</span><br><span class="line">      2001         10      69.05      256.7</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h3 id="last-value"><a href="#last-value" class="headerlink" title="last_value"></a>last_value</h3><p>last_value返回窗口中的最后一个值。respect nulls表示识别空值，如果最后一个是空值也将其返回。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   last_value(sale respect nulls) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale desc</span><br><span class="line">   rows between unbounded preceding and unbounded following</span><br><span class="line">   ) min_sale</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE   MIN_SALE</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      1998          1      58.15      29.39</span><br><span class="line">      1998          2      29.39      29.39</span><br><span class="line">      1998          3      29.49      29.39</span><br><span class="line">      1998          4      29.49      29.39</span><br><span class="line">      1998          5       29.8      29.39</span><br><span class="line">      1998          6      58.78      29.39</span><br><span class="line">      1998          9      58.78      29.39</span><br><span class="line">      1998         10     117.76      29.39</span><br><span class="line">      1999          1      53.52       40.5</span><br><span class="line">      1999          3       94.6       40.5</span><br><span class="line">      1999          4       40.5       40.5</span><br><span class="line">      1999          5      80.01       40.5</span><br><span class="line">      1999          6       40.5       40.5</span><br><span class="line">      1999          8     103.11       40.5</span><br><span class="line">      1999          9      53.34       40.5</span><br><span class="line">      1999         10         72       40.5</span><br><span class="line">      2000          1       46.7      46.54</span><br><span class="line">      2000          3      93.41      46.54</span><br><span class="line">      2000          4      46.54      46.54</span><br><span class="line">      2000          5       46.7      46.54</span><br><span class="line">      2000          7       70.8      46.54</span><br><span class="line">      2000          8      46.54      46.54</span><br><span class="line">      2001          1      92.26      22.44</span><br><span class="line">      2001          2     118.38      22.44</span><br><span class="line">      2001          3      47.24      22.44</span><br><span class="line">      2001          4      256.7      22.44</span><br><span class="line">      2001          5      93.44      22.44</span><br><span class="line">      2001          6      22.44      22.44</span><br><span class="line">      2001          7      69.96      22.44</span><br><span class="line">      2001          8      46.06      22.44</span><br><span class="line">      2001          9      92.67      22.44</span><br><span class="line">      2001         10      69.05      22.44</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="nth-value"><a href="#nth-value" class="headerlink" title="nth_value"></a>nth_value</h2><p>nth_value函数用于返回任意行的数据，语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">nth_value(measure, n) [from first | from last] [respect nulls | ignore nulls]</span><br><span class="line">over(partition-clause order-by-clause windowing-clause)</span><br></pre></td></tr></table></figure><p></p><p>比如<code>first_value(sale)</code>等价于<code>nth_value(sale,1)</code>；<code>first_value(sale ignore nulls)</code>等价于<code>nth_value(sale,1) from first ignore nulls</code>。</p><p>比如求得分区内第二大的sale值大小：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   nth_value(sale,2) ignore nulls over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale desc</span><br><span class="line">   rows between unbounded preceding and unbounded following</span><br><span class="line">   ) second_max_sale</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE SECOND_MAX_SALE</span><br><span class="line"><span class="comment">---------- ---------- ---------- ---------------</span></span><br><span class="line">      1998          1      58.15           58.78</span><br><span class="line">      1998          2      29.39           58.78</span><br><span class="line">      1998          3      29.49           58.78</span><br><span class="line">      1998          4      29.49           58.78</span><br><span class="line">      1998          5       29.8           58.78</span><br><span class="line">      1998          6      58.78           58.78</span><br><span class="line">      1998          9      58.78           58.78</span><br><span class="line">      1998         10     117.76           58.78</span><br><span class="line">      1999          1      53.52            94.6</span><br><span class="line">      1999          3       94.6            94.6</span><br><span class="line">      1999          4       40.5            94.6</span><br><span class="line">      1999          5      80.01            94.6</span><br><span class="line">      1999          6       40.5            94.6</span><br><span class="line">      1999          8     103.11            94.6</span><br><span class="line">      1999          9      53.34            94.6</span><br><span class="line">      1999         10         72            94.6</span><br><span class="line">      2000          1       46.7            70.8</span><br><span class="line">      2000          3      93.41            70.8</span><br><span class="line">      2000          4      46.54            70.8</span><br><span class="line">      2000          5       46.7            70.8</span><br><span class="line">      2000          7       70.8            70.8</span><br><span class="line">      2000          8      46.54            70.8</span><br><span class="line">      2001          1      92.26          118.38</span><br><span class="line">      2001          2     118.38          118.38</span><br><span class="line">      2001          3      47.24          118.38</span><br><span class="line">      2001          4      256.7          118.38</span><br><span class="line">      2001          5      93.44          118.38</span><br><span class="line">      2001          6      22.44          118.38</span><br><span class="line">      2001          7      69.96          118.38</span><br><span class="line">      2001          8      46.06          118.38</span><br><span class="line">      2001          9      92.67          118.38</span><br><span class="line">      2001         10      69.05          118.38</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="rank"><a href="#rank" class="headerlink" title="rank"></a>rank</h2><p>rank函数以数值的形式返回一个数据行在排序后的结果集中的位置。在排名并列的情况下，具有相同值的行将具有相同的排名，并且接下来的排名会被跳过。</p><p>rank函数不支持开窗子句，作用于整个分区：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select * from (</span><br><span class="line">   <span class="keyword">select</span> <span class="keyword">year</span>,<span class="keyword">week</span>,sale,</span><br><span class="line">   <span class="keyword">rank</span>() <span class="keyword">over</span>(</span><br><span class="line">   <span class="keyword">partition</span> <span class="keyword">by</span> product,country,region,<span class="keyword">year</span></span><br><span class="line">   <span class="keyword">order</span> <span class="keyword">by</span> sale <span class="keyword">desc</span></span><br><span class="line">   ) sales_rank</span><br><span class="line">   <span class="keyword">from</span> sales_fact</span><br><span class="line">   <span class="keyword">where</span> country <span class="keyword">in</span> (<span class="string">'Australia'</span>) <span class="keyword">and</span> product = <span class="string">'Xtend Memory'</span> <span class="keyword">and</span> <span class="keyword">week</span> &lt;= <span class="number">10</span></span><br><span class="line">   <span class="keyword">order</span> <span class="keyword">by</span> product,country,<span class="keyword">year</span>,<span class="keyword">week</span></span><br><span class="line">   ) <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">year</span>,sales_rank;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE SALES_RANK</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      1998         10     117.76          1</span><br><span class="line">      1998          6      58.78          2</span><br><span class="line">      1998          9      58.78          2</span><br><span class="line">      1998          1      58.15          4</span><br><span class="line">      1998          5       29.8          5</span><br><span class="line">      1998          4      29.49          6</span><br><span class="line">      1998          3      29.49          6</span><br><span class="line">      1998          2      29.39          8</span><br><span class="line">      1999          8     103.11          1</span><br><span class="line">      1999          3       94.6          2</span><br><span class="line">      1999          5      80.01          3</span><br><span class="line">      1999         10         72          4</span><br><span class="line">      1999          1      53.52          5</span><br><span class="line">      1999          9      53.34          6</span><br><span class="line">      1999          4       40.5          7</span><br><span class="line">      1999          6       40.5          7</span><br><span class="line">      2000          3      93.41          1</span><br><span class="line">      2000          7       70.8          2</span><br><span class="line">      2000          1       46.7          3</span><br><span class="line">      2000          5       46.7          3</span><br><span class="line">      2000          4      46.54          5</span><br><span class="line">      2000          8      46.54          5</span><br><span class="line">      2001          4      256.7          1</span><br><span class="line">      2001          2     118.38          2</span><br><span class="line">      2001          5      93.44          3</span><br><span class="line">      2001          9      92.67          4</span><br><span class="line">      2001          1      92.26          5</span><br><span class="line">      2001          7      69.96          6</span><br><span class="line">      2001         10      69.05          7</span><br><span class="line">      2001          3      47.24          8</span><br><span class="line">      2001          8      46.06          9</span><br><span class="line">      2001          6      22.44         10</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="dense-rank"><a href="#dense-rank" class="headerlink" title="dense_rank"></a>dense_rank</h2><p>dense_rank函数是rank函数的变体，区别在于dense_rank函数的排名值是连续的：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select * from (</span><br><span class="line">   <span class="keyword">select</span> <span class="keyword">year</span>,<span class="keyword">week</span>,sale,</span><br><span class="line">   <span class="keyword">dense_rank</span>() <span class="keyword">over</span>(</span><br><span class="line">   <span class="keyword">partition</span> <span class="keyword">by</span> product,country,region,<span class="keyword">year</span></span><br><span class="line">   <span class="keyword">order</span> <span class="keyword">by</span> sale <span class="keyword">desc</span></span><br><span class="line">   ) sales_dense_rank</span><br><span class="line">   <span class="keyword">from</span> sales_fact</span><br><span class="line">   <span class="keyword">where</span> country <span class="keyword">in</span> (<span class="string">'Australia'</span>) <span class="keyword">and</span> product = <span class="string">'Xtend Memory'</span> <span class="keyword">and</span> <span class="keyword">week</span> &lt;= <span class="number">10</span></span><br><span class="line">   <span class="keyword">order</span> <span class="keyword">by</span> product,country,<span class="keyword">year</span>,<span class="keyword">week</span></span><br><span class="line">   ) <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">year</span>,sales_dense_rank;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE SALES_DENSE_RANK</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------------</span></span><br><span class="line">      1998         10     117.76                1</span><br><span class="line">      1998          6      58.78                2</span><br><span class="line">      1998          9      58.78                2</span><br><span class="line">      1998          1      58.15                3</span><br><span class="line">      1998          5       29.8                4</span><br><span class="line">      1998          4      29.49                5</span><br><span class="line">      1998          3      29.49                5</span><br><span class="line">      1998          2      29.39                6</span><br><span class="line">      1999          8     103.11                1</span><br><span class="line">      1999          3       94.6                2</span><br><span class="line">      1999          5      80.01                3</span><br><span class="line">      1999         10         72                4</span><br><span class="line">      1999          1      53.52                5</span><br><span class="line">      1999          9      53.34                6</span><br><span class="line">      1999          4       40.5                7</span><br><span class="line">      1999          6       40.5                7</span><br><span class="line">      2000          3      93.41                1</span><br><span class="line">      2000          7       70.8                2</span><br><span class="line">      2000          1       46.7                3</span><br><span class="line">      2000          5       46.7                3</span><br><span class="line">      2000          4      46.54                4</span><br><span class="line">      2000          8      46.54                4</span><br><span class="line">      2001          4      256.7                1</span><br><span class="line">      2001          2     118.38                2</span><br><span class="line">      2001          5      93.44                3</span><br><span class="line">      2001          9      92.67                4</span><br><span class="line">      2001          1      92.26                5</span><br><span class="line">      2001          7      69.96                6</span><br><span class="line">      2001         10      69.05                7</span><br><span class="line">      2001          3      47.24                8</span><br><span class="line">      2001          8      46.06                9</span><br><span class="line">      2001          6      22.44               10</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="row-number"><a href="#row-number" class="headerlink" title="row_number"></a>row_number</h2><p>row_number函数为结果集中的每一行分配一个递增行编号，支持开窗子句。如果存在值相同的数据行，谁先谁后具有不确定性。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   row_number() over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale</span><br><span class="line">   ) sales_rn</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,sale;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE   SALES_RN</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      1998          2      29.39          1</span><br><span class="line">      1998          3      29.49          2</span><br><span class="line">      1998          4      29.49          3</span><br><span class="line">      1998          5       29.8          4</span><br><span class="line">      1998          1      58.15          5</span><br><span class="line">      1998          9      58.78          6</span><br><span class="line">      1998          6      58.78          7</span><br><span class="line">      1998         10     117.76          8</span><br><span class="line">      1999          4       40.5          1</span><br><span class="line">      1999          6       40.5          2</span><br><span class="line">      1999          9      53.34          3</span><br><span class="line">      1999          1      53.52          4</span><br><span class="line">      1999         10         72          5</span><br><span class="line">      1999          5      80.01          6</span><br><span class="line">      1999          3       94.6          7</span><br><span class="line">      1999          8     103.11          8</span><br><span class="line">      2000          4      46.54          1</span><br><span class="line">      2000          8      46.54          2</span><br><span class="line">      2000          5       46.7          3</span><br><span class="line">      2000          1       46.7          4</span><br><span class="line">      2000          7       70.8          5</span><br><span class="line">      2000          3      93.41          6</span><br><span class="line">      2001          6      22.44          1</span><br><span class="line">      2001          8      46.06          2</span><br><span class="line">      2001          3      47.24          3</span><br><span class="line">      2001         10      69.05          4</span><br><span class="line">      2001          7      69.96          5</span><br><span class="line">      2001          1      92.26          6</span><br><span class="line">      2001          9      92.67          7</span><br><span class="line">      2001          5      93.44          8</span><br><span class="line">      2001          2     118.38          9</span><br><span class="line">      2001          4      256.7         10</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="ratio-to-report"><a href="#ratio-to-report" class="headerlink" title="ratio_to_report"></a>ratio_to_report</h2><p>ratio_to_report函数用于计算当前行的值占分区总和的值的百分比，该函数没有排序和开窗子句。</p><p>比如计算当前周的销售额在该年以及所有销售额中的百分比：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   trunc(100*ratio_to_report(sale) over(partition by product,country,region,year),2) || '%' p1,</span><br><span class="line">   trunc(100*ratio_to_report(sale) over(partition by product,country,region),2) || '%' p2</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week &lt;= 10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE P1              P2                </span><br><span class="line"><span class="comment">---------- ---------- ---------- --------------- -----------------</span></span><br><span class="line">      1998          1      58.15 14.12%          2.63%</span><br><span class="line">      1998          2      29.39 7.13%           1.33%</span><br><span class="line">      1998          3      29.49 7.16%           1.33%</span><br><span class="line">      1998          4      29.49 7.16%           1.33%</span><br><span class="line">      1998          5       29.8 7.23%           1.34%</span><br><span class="line">      1998          6      58.78 14.27%          2.66%</span><br><span class="line">      1998          9      58.78 14.27%          2.66%</span><br><span class="line">      1998         10     117.76 28.6%           5.33%</span><br><span class="line">      1999          1      53.52 9.95%           2.42%</span><br><span class="line">      1999          3       94.6 17.59%          4.28%</span><br><span class="line">      1999          4       40.5 7.53%           1.83%</span><br><span class="line">      1999          5      80.01 14.88%          3.62%</span><br><span class="line">      1999          6       40.5 7.53%           1.83%</span><br><span class="line">      1999          8     103.11 19.18%          4.66%</span><br><span class="line">      1999          9      53.34 9.92%           2.41%</span><br><span class="line">      1999         10         72 13.39%          3.26%</span><br><span class="line">      2000          1       46.7 13.31%          2.11%</span><br><span class="line">      2000          3      93.41 26.63%          4.23%</span><br><span class="line">      2000          4      46.54 13.27%          2.1%</span><br><span class="line">      2000          5       46.7 13.31%          2.11%</span><br><span class="line">      2000          7       70.8 20.18%          3.2%</span><br><span class="line">      2000          8      46.54 13.27%          2.1%</span><br><span class="line">      2001          1      92.26 10.15%          4.17%</span><br><span class="line">      2001          2     118.38 13.03%          5.36%</span><br><span class="line">      2001          3      47.24 5.2%            2.13%</span><br><span class="line">      2001          4      256.7 28.26%          11.62%</span><br><span class="line">      2001          5      93.44 10.28%          4.23%</span><br><span class="line">      2001          6      22.44 2.47%           1.01%</span><br><span class="line">      2001          7      69.96 7.7%            3.16%</span><br><span class="line">      2001          8      46.06 5.07%           2.08%</span><br><span class="line">      2001          9      92.67 10.2%           4.19%</span><br><span class="line">      2001         10      69.05 7.6%            3.12%</span><br><span class="line"></span><br><span class="line">已选择32行。</span><br></pre></td></tr></table></figure><p></p><h2 id="ntile"><a href="#ntile" class="headerlink" title="ntile"></a>ntile</h2><p>ntile函数对一个分区中的有序结果集进行划分，分为若干个组，如果不能够等分，则每个组中相差的数据行不能超过一行，并为每个小组分配唯一的组编号。该函数不支持开窗子句。</p><p>比如将2001年的销售额数据行分为十个小组：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   ntile(10) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale</span><br><span class="line">   ) group<span class="comment">#</span></span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and year=2001;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE     GROUP<span class="comment">#</span></span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      2001         18      22.37          1</span><br><span class="line">      2001         23      22.38          1</span><br><span class="line">      2001          6      22.44          1</span><br><span class="line">      2001         52      23.14          1</span><br><span class="line">      2001         50      23.14          1</span><br><span class="line">      2001         44      23.29          2</span><br><span class="line">      2001         40      45.18          2</span><br><span class="line">      2001         49      45.26          2</span><br><span class="line">      2001          8      46.06          2</span><br><span class="line">      2001          3      47.24          2</span><br><span class="line">      2001         41      67.19          3</span><br><span class="line">      2001         34       68.9          3</span><br><span class="line">      2001         32       68.9          3</span><br><span class="line">      2001         10      69.05          3</span><br><span class="line">      2001          7      69.96          3</span><br><span class="line">      2001         11      71.57          4</span><br><span class="line">      2001         36      91.12          4</span><br><span class="line">      2001         15      91.98          4</span><br><span class="line">      2001         31      92.21          4</span><br><span class="line">      2001          1      92.26          4</span><br><span class="line">      2001          9      92.67          5</span><br><span class="line">      2001         37      93.16          5</span><br><span class="line">      2001          5      93.44          5</span><br><span class="line">      2001         46      93.58          5</span><br><span class="line">      2001         27      94.48          5</span><br><span class="line">      2001         51     114.82          6</span><br><span class="line">      2001         33     115.52          6</span><br><span class="line">      2001         39     115.57          6</span><br><span class="line">      2001         13     116.81          6</span><br><span class="line">      2001         12     116.81          7</span><br><span class="line">      2001         29     116.85          7</span><br><span class="line">      2001         20     118.03          7</span><br><span class="line">      2001          2     118.38          7</span><br><span class="line">      2001         24     136.92          8</span><br><span class="line">      2001         42     136.98          8</span><br><span class="line">      2001         38        139          8</span><br><span class="line">      2001         25     139.28          8</span><br><span class="line">      2001         43     139.58          9</span><br><span class="line">      2001         22     141.78          9</span><br><span class="line">      2001         14     162.91          9</span><br><span class="line">      2001         30     162.91          9</span><br><span class="line">      2001         48     182.96         10</span><br><span class="line">      2001         21      233.7         10</span><br><span class="line">      2001          4      256.7         10</span><br><span class="line">      2001         16     278.44         10</span><br><span class="line"></span><br><span class="line">已选择45行。</span><br></pre></td></tr></table></figure><p></p><h2 id="stddev"><a href="#stddev" class="headerlink" title="stddev"></a>stddev</h2><p>stddev函数用于计算某些数据行在分区中的标准差：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select year,week,sale,</span><br><span class="line">   stddev(sale) over(</span><br><span class="line">   partition by product,country,region,year</span><br><span class="line">   order by sale</span><br><span class="line">   rows between unbounded preceding and unbounded following</span><br><span class="line">   ) stddev</span><br><span class="line">   from sales_fact</span><br><span class="line">   where country in ('Australia') and product = 'Xtend Memory' and week&lt;10</span><br><span class="line">   order by product,country,year,week;</span><br><span class="line"></span><br><span class="line">      YEAR       WEEK       SALE     STDDEV</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      1998          1      58.15  15.517783</span><br><span class="line">      1998          2      29.39  15.517783</span><br><span class="line">      1998          3      29.49  15.517783</span><br><span class="line">      1998          4      29.49  15.517783</span><br><span class="line">      1998          5       29.8  15.517783</span><br><span class="line">      1998          6      58.78  15.517783</span><br><span class="line">      1998          9      58.78  15.517783</span><br><span class="line">      1999          1      53.52 25.8395281</span><br><span class="line">      1999          3       94.6 25.8395281</span><br><span class="line">      1999          4       40.5 25.8395281</span><br><span class="line">      1999          5      80.01 25.8395281</span><br><span class="line">      1999          6       40.5 25.8395281</span><br><span class="line">      1999          8     103.11 25.8395281</span><br><span class="line">      1999          9      53.34 25.8395281</span><br><span class="line">      2000          1       46.7  19.670004</span><br><span class="line">      2000          3      93.41  19.670004</span><br><span class="line">      2000          4      46.54  19.670004</span><br><span class="line">      2000          5       46.7  19.670004</span><br><span class="line">      2000          7       70.8  19.670004</span><br><span class="line">      2000          8      46.54  19.670004</span><br><span class="line">      2001          1      92.26  68.235866</span><br><span class="line">      2001          2     118.38  68.235866</span><br><span class="line">      2001          3      47.24  68.235866</span><br><span class="line">      2001          4      256.7  68.235866</span><br><span class="line">      2001          5      93.44  68.235866</span><br><span class="line">      2001          6      22.44  68.235866</span><br><span class="line">      2001          7      69.96  68.235866</span><br><span class="line">      2001          8      46.06  68.235866</span><br><span class="line">      2001          9      92.67  68.235866</span><br><span class="line"></span><br><span class="line">已选择29行。</span><br></pre></td></tr></table></figure><p></p><h2 id="listagg"><a href="#listagg" class="headerlink" title="listagg"></a>listagg</h2><p>listagg函数将分区中多个数据行中的某列的值以某个符号拼接成一行，语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">listagg(string,separator) within group(order-by-clause) over(partition-by-clause)</span><br></pre></td></tr></table></figure><p></p><p><code>within group(order-by-clause)</code>子句声明排序顺序。</p><p>比如将sales_fact数据表中的country转化为以逗号分隔的一行值：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; col country_string for a50</span><br><span class="line">SQL&gt; select listagg(country,',')</span><br><span class="line">  within group(order by country desc) country_string</span><br><span class="line">  from(</span><br><span class="line">    <span class="keyword">select</span> <span class="keyword">distinct</span> country <span class="keyword">from</span> sales_fact</span><br><span class="line">  );</span><br><span class="line"></span><br><span class="line">COUNTRY_STRING</span><br><span class="line"><span class="comment">--------------------------------------------------</span></span><br><span class="line">United States of America,United Kingdom,Turkey,Spa</span><br><span class="line">in,Singapore,Saudi Arabia,Poland,New Zealand,Japan</span><br><span class="line">,Italy,Germany,France,Denmark,China,Canada,Brazil,</span><br><span class="line">Australia,Argentina</span><br></pre></td></tr></table></figure><p></p><script>$(".post-body a").not(".thispage").addClass("ignore-href").attr("target","_blank")</script></div><div></div><div><div style="padding:10px 0;margin:20px auto;width:90%;text-align:center;color:#878787"><div>请作者喝瓶肥宅水~</div><button id="rewardButton" style="margin-top:10px" disable="enable" onclick='var e=document.getElementById("QR");"none"===e.style.display?e.style.display="block":e.style.display="none"'><span style="height:46px;width:46px;line-height:46px;border-radius:50%;color:#fe5f55;font-weight:600;background:#ffd5be;border:none;box-shadow:0 4px 8px 0 rgba(255,213,190,.4)">￥</span></button><div id="QR" style="display:none"><div id="wechat" style="display:inline-block"><img id="wechat_qr" src="/img/wechat_pay.png" alt="MrBird WeChat Pay"></div><div id="alipay" style="display:inline-block"><img id="alipay_qr" src="/img/ali_pay.png" alt="MrBird Alipay"></div></div></div><style>#QR img{width:auto;margin:.8em 1em 0 1em}</style></div><div><ul class="post-copyright"><li class="post-copyright-author"><strong>本文作者：</strong> MrBird</li><li class="post-copyright-link"><strong>本文链接：</strong> <a href="http://mrbird.cc/oracle Window function.html" title="Oracle分析函数（窗口函数）">http://mrbird.cc/oracle Window function.html</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li></ul></div><footer class="post-footer"><div class="post-tags" style="margin-bottom:1.3rem"><a href="/tags/DataBase/" rel="tag"># DataBase</a> <a href="/tags/Oracle/" rel="tag"># Oracle</a> <a href="/tags/Oracle-11g/" rel="tag"># Oracle 11g</a></div><div class="post-nav"><div class="post-nav-next post-nav-item"><a href="/Oracle-Object-Type.html" rel="next" title="Oracle对象类型"><i class="fa fa-chevron-left"></i> Oracle对象类型</a></div><span class="post-nav-divider"></span><div class="post-nav-prev post-nav-item"><a href="/Oracle-Merge语句.html" rel="prev" title="Oracle Merge语句">Oracle Merge语句 <i class="fa fa-chevron-right"></i></a></div></div></footer></article><hr><div id="container"></div><div class="post-spread"><div id="comment-div"></div><style>.valine .vlist{margin-bottom:3rem}.valine .vwrap .vcontrol .col.col-60{text-align:left}.valine .vlist .vcard .vhead,.valine .vlist .vcard section .vfooter{text-align:left}.valine .vlist .vcard section{padding-bottom:.5rem!important}.vname{color:#42b983!important}.valine .vinfo .col{text-align:left;margin-left:-27rem}div#comment-div{margin-bottom:-8rem}.valine .vlist .vcard .vcontent .code,.valine .vlist .vcard .vcontent code,.valine .vlist .vcard .vcontent pre{background:#fbfbfb}.valine .vlist .vcard .vcontent a{color:#42b983}.valine .vlist .vcard .vimg{border-radius:3px}.valine .vbtn{border-radius:2px;padding:.3rem 1.25rem}.valine .vbtn:active,.valine .vbtn:hover{color:#42b983;border-color:#42b983;background-color:#fff}.valine .vwrap .vheader .vinput:focus{border-bottom-color:#42b983}.valine .vlist .vcard .vcontent.expand:before{background:-webkit-gradient(linear,left top,left bottom,from(hsla(0,0%,100%,0)),to(hsla(0,0%,100%,.2)));background:linear-gradient(180deg,hsla(0,0%,100%,0),hsla(0,0%,100%,.2))}.valine .vlist .vcard .vcontent.expand:after{content:"点击展开";font-size:.4rem;text-align:right;left:-1rem;background:hsla(0,0%,100%,.2)}.valine .vlist .vcard section .vfooter .vat{color:#b3b3b3}.valine .vlist .vcard section .vfooter .vat:hover{color:#42b983}.vcontent img{margin:0}.valine .info{display:none}</style><script type="text/javascript" src="/js/av.min.js"></script><script type="text/javascript" src="/js/Valine.min.js"></script><script type="text/javascript" src="/js/activate-power-mode.js"></script><script>POWERMODE.colorful=!0,POWERMODE.shake=!1,document.body.addEventListener("input",POWERMODE),new Valine({el:"#comment-div",notify:!1,verify:!0,appId:"SMcDFP1bN1jgb9Lo8JmtICHm-gzGzoHsz",appKey:"dH4nrUrt3V5XiJiI6Qyejnbi",placeholder:"",path:window.location.pathname,avatar:"monsterid",guest_info:["nick","mail","link"]});var chicken='<a href="#"><img src="https://image.uisdc.com/wp-content/uploads/2018/06/uisdc-chat-chicken.gif" class="checken"></a>';$("#comment-div").prepend(chicken)</script></div></div><script>var $bqinline=$("img[alt='bq-inline']");$bqinline.css({width:"2.3rem",height:"2.3rem",display:"inline","vertical-align":"text-bottom"})</script></div><div class="comments" id="comments"></div></div><aside id="sidebar" class="sidebar" onselectstart="return!1"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">Contents</li><li class="sidebar-nav-overview" data-target="site-overview">Site Preview</li></ul><section class="site-overview sidebar-panel"><div class="sidebar-sticky sticky"><div itemscope itemtype="http://schema.org/Person"><div class="author__avatar"><img src="/images/blogImage.jpg" class="author__avatar" alt="MrBird" itemprop="image"></div><div class="author__content"><h3 class="author__name" itemprop="name">MrBird's Blog</h3><p class="author__bio" itemprop="description">A simple blog, code repository, just keep blogging</p></div><div class="author__urls-wrapper"><button class="btn btn--inverse">Follow</button><ul class="author__urls social-icons"><li><a href="http://map.baidu.com/?newmap=1&s=s%26wd%3D%E7%A6%8F%E5%B7%9E%E5%B8%82%26c%3D300&from=alamap&tpl=mapcity" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-map-marker" aria-hidden="true"></i>&nbsp;&nbsp;FuZhou,CN</a></li><li><a href="https://love.mrbird.cc" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-diamond" aria-hidden="true"></i>&nbsp;&nbsp;Love</a></li><li><a href="https://cloud.mrbird.cn" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-chain" aria-hidden="true"></i>&nbsp;&nbsp;FEBS</a></li><li><a href="/atom.xml" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-rss" aria-hidden="true"></i>&nbsp;&nbsp;RSS</a></li><li><a href="https://gitee.com/mrbirdd" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-codepen" aria-hidden="true"></i>&nbsp;&nbsp;Gitee</a></li><li><a href="https://github.com/wuyouzhuguli" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-github-alt" aria-hidden="true"></i>&nbsp;&nbsp;GitHub</a></li><li><a href="javascript:;" class="popup-trigger"><i class="fa fa-fw fa-search" aria-hidden="true"></i>&nbsp;&nbsp;Search</a></li></ul></div></div></div><script>var $urls=$(".author__urls").find("a");$urls.each(function(){var o=$(this);o.mouseenter(function(){o.css({color:"#414547"})}),o.mouseleave(function(){o.css({color:""})})})</script></section><section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active"><div class="post-toc"><div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#分析模式下的聚合函数"><span class="nav-number">1.</span> <span class="nav-text">分析模式下的聚合函数</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#lag和lead"><span class="nav-number">2.</span> <span class="nav-text">lag和lead</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#lag"><span class="nav-number">2.1.</span> <span class="nav-text">lag</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#lead"><span class="nav-number">2.2.</span> <span class="nav-text">lead</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#first-value和last-value"><span class="nav-number">3.</span> <span class="nav-text">first_value和last_value</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#first-value"><span class="nav-number">3.1.</span> <span class="nav-text">first_value</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#last-value"><span class="nav-number">3.2.</span> <span class="nav-text">last_value</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#nth-value"><span class="nav-number">4.</span> <span class="nav-text">nth_value</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#rank"><span class="nav-number">5.</span> <span class="nav-text">rank</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#dense-rank"><span class="nav-number">6.</span> <span class="nav-text">dense_rank</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#row-number"><span class="nav-number">7.</span> <span class="nav-text">row_number</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#ratio-to-report"><span class="nav-number">8.</span> <span class="nav-text">ratio_to_report</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#ntile"><span class="nav-number">9.</span> <span class="nav-text">ntile</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#stddev"><span class="nav-number">10.</span> <span class="nav-text">stddev</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#listagg"><span class="nav-number">11.</span> <span class="nav-text">listagg</span></a></li></ol></div></div></section></div></aside></div></main><footer id="footer" class="footer" onselectstart="return!1"><div class="footer-inner"><div class="copyright">&copy; 2016 - <span itemprop="copyrightYear">2019</span>&nbsp;&nbsp; <span class="author" itemprop="copyrightHolder">MrBird</span>&nbsp;&nbsp;|<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>&nbsp;&nbsp;UV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_uv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span> &nbsp;&nbsp;PV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_pv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span></div></div></footer><div class="back-to-top"><span style="font-family:'Source Sans Pro','Helvetica Neue',Arial,sans-serif;font-size:1.2em;font-weight:600">TOP</span></div></div><script type="text/javascript">"[object Function]"!==Object.prototype.toString.call(window.Promise)&&(window.Promise=null)</script><script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script><script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script><script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script><script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script><script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script><script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script><script type="text/javascript">function proceedsearch(){$("body").append('<div class="search-popup-overlay local-search-pop-overlay"></div>').css("overflow","hidden"),$(".search-popup-overlay").click(onPopupClose),$(".popup").toggle();var t=$("#local-search-input");t.attr("autocapitalize","none"),t.attr("autocorrect","off"),t.focus()}var isfetched=!1,isXml=!0,search_path="search.xml";0===search_path.length?search_path="search.xml":search_path.endsWith("json")&&(isXml=!1);var path="/"+search_path,onPopupClose=function(t){$(".popup").hide(),$("#local-search-input").val(""),$(".search-result-list").remove(),$("#no-result").remove(),$(".local-search-pop-overlay").remove(),$("body").css("overflow","")},searchFunc=function(t,e,o){"use strict";$("body").append('<div class="search-popup-overlay local-search-pop-overlay"><div id="search-loading-icon"><i class="fa fa-spinner fa-pulse fa-2x fa-fw"></i></div></div>').css("overflow","hidden"),$("#search-loading-icon").css("margin","20% auto 0 auto").css("text-align","center"),$.ajax({url:t,dataType:isXml?"xml":"json",async:!0,success:function(t){isfetched=!0,$(".popup").detach().appendTo(".header-inner");var n=isXml?$("entry",t).map(function(){return{title:$("title",this).text(),content:$("content",this).text(),url:$("url",this).text()}}).get():t,r=document.getElementById(e),s=document.getElementById(o),a=function(){var t=r.value.trim().toLowerCase(),e=t.split(/[\s\-]+/);e.length>1&&e.push(t);var o=[];if(t.length>0&&n.forEach(function(n){function r(e,o,n,r){for(var s=r[r.length-1],a=s.position,i=s.word,l=[],h=0;a+i.length<=n&&0!=r.length;){i===t&&h++,l.push({position:a,length:i.length});var p=a+i.length;for(r.pop();0!=r.length&&(s=r[r.length-1],a=s.position,i=s.word,p>a);)r.pop()}return c+=h,{hits:l,start:o,end:n,searchTextCount:h}}function s(t,e){var o="",n=e.start;return e.hits.forEach(function(e){o+=t.substring(n,e.position);var r=e.position+e.length;o+='<b class="search-keyword">'+t.substring(e.position,r)+"</b>",n=r}),o+=t.substring(n,e.end)}var a=!1,i=0,c=0,l=n.title.trim(),h=l.toLowerCase(),p=n.content.trim().replace(/<[^>]+>/g,""),u=p.toLowerCase(),f=decodeURIComponent(n.url),d=[],g=[];if(""!=l&&(e.forEach(function(t){function e(t,e,o){var n=t.length;if(0===n)return[];var r=0,s=[],a=[];for(o||(e=e.toLowerCase(),t=t.toLowerCase());(s=e.indexOf(t,r))>-1;)a.push({position:s,word:t}),r=s+n;return a}d=d.concat(e(t,h,!1)),g=g.concat(e(t,u,!1))}),(d.length>0||g.length>0)&&(a=!0,i=d.length+g.length)),a){[d,g].forEach(function(t){t.sort(function(t,e){return e.position!==t.position?e.position-t.position:t.word.length-e.word.length})});var v=[];0!=d.length&&v.push(r(l,0,l.length,d));for(var C=[];0!=g.length;){var $=g[g.length-1],m=$.position,x=$.word,w=m-20,y=m+80;w<0&&(w=0),y<m+x.length&&(y=m+x.length),y>p.length&&(y=p.length),C.push(r(p,w,y,g))}C.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hits.length!==e.hits.length?e.hits.length-t.hits.length:t.start-e.start});var T=parseInt("1");T>=0&&(C=C.slice(0,T));var b="";b+=0!=v.length?"<li><a href='"+f+"' class='search-result-title'>"+s(l,v[0])+"</a>":"<li><a href='"+f+"' class='search-result-title'>"+l+"</a>",C.forEach(function(t){b+="<a href='"+f+'\'><p class="search-result">'+s(p,t)+"...</p></a>"}),b+="</li>",o.push({item:b,searchTextCount:c,hitCount:i,id:o.length})}}),1===e.length&&""===e[0])s.innerHTML='<div id="no-result"><i class="fa fa-search fa-5x" /></div>';else if(0===o.length)s.innerHTML='<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>';else{o.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hitCount!==e.hitCount?e.hitCount-t.hitCount:e.id-t.id});var a='<ul class="search-result-list">';o.forEach(function(t){a+=t.item}),a+="</ul>",s.innerHTML=a}};r.addEventListener("input",a),$(".local-search-pop-overlay").remove(),$("body").css("overflow",""),proceedsearch()}})};$(".popup-trigger").click(function(t){t.stopPropagation(),isfetched===!1?searchFunc(path,"local-search-input","local-search-result"):proceedsearch()}),$(".popup-btn-close").click(onPopupClose),$(".popup").click(function(t){t.stopPropagation()}),$(document).on("keyup",function(t){var e=27===t.which&&$(".search-popup").is(":visible");e&&onPopupClose()})</script></body><script>$(function(){$("a").not(".nav-link,.cloud-tie-join-count,.ignore-href,.jstree-anchor").addClass("animsition-link")});var burst1=new mojs.Burst({left:0,top:0,radius:{5:40},children:{shape:"circle",fill:["red","cyan","orange"],fillOpacity:.8,radiusX:3.5,radiusY:3.5}});document.addEventListener("click",function(a){null==a.target.href&&"footer"!=a.target.className&&"copyright"!=a.target.className&&"author__urls social-icons"!=a.target.className&&"author__avatar"!=a.target.className&&"sidebar sidebar-active"!=a.target.className&&burst1.tune({x:a.pageX,y:a.pageY}).generate().replay()})</script><script type="text/javascript" src="/js/message.js"></script></html><!-- rebuild by neat -->